
--Create database

USE [master]
GO
IF NOT EXISTS (SELECT [name] FROM sys.databases WHERE name = N'SuperStar_Nhom11')
BEGIN
CREATE DATABASE [SuperStar_Nhom11] COLLATE SQL_Latin1_General_CP1_CI_AS
END
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'SuperStar_Nhom11', @new_cmptlevel=100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
	EXEC [SuperStar_Nhom11].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [SuperStar_Nhom11] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET ARITHABORT OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SuperStar_Nhom11] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SuperStar_Nhom11] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SuperStar_Nhom11] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET DISABLE_BROKER
GO
ALTER DATABASE [SuperStar_Nhom11] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SuperStar_Nhom11] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SuperStar_Nhom11] SET READ_WRITE
GO
ALTER DATABASE [SuperStar_Nhom11] SET RECOVERY SIMPLE
GO
ALTER DATABASE [SuperStar_Nhom11] SET MULTI_USER
GO
ALTER DATABASE [SuperStar_Nhom11] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SuperStar_Nhom11] SET DB_CHAINING OFF
GO

USE [SuperStar_Nhom11]
GO

--Table dbo.Applications

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Applications] (
	[ApplicationName] [nvarchar](235) NOT NULL,
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[Description] [nvarchar](256) NULL);
GO

INSERT INTO [dbo].[Applications] ([ApplicationName], [ApplicationId], [Description])
	VALUES (N'/', CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), NULL)

GO

--Table dbo.Bill

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Bill] (
	[Id] [int] NOT NULL IDENTITY (1, 1),
	[Customer_Id] [uniqueidentifier] NOT NULL,
	[Reseller_Id] [int] NULL,
	[Product_Id] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
	[Price] [real] NOT NULL,
	[GiftPoint] [int] NOT NULL,
	[Status_Id] [int] NOT NULL,
	[ReceivedDate] [datetime] NULL,
	[DateAdded] [datetime] NOT NULL);
GO

SET IDENTITY_INSERT [dbo].[Bill] ON
GO
INSERT INTO [dbo].[Bill] ([Id], [Customer_Id], [Reseller_Id], [Product_Id], [Quantity], [Price], [GiftPoint], [Status_Id], [ReceivedDate], [DateAdded])
	VALUES (1, CAST ('a2634e86-e7e1-4812-a896-0b8b9f2b3a08' AS uniqueidentifier), 1, 1, 1, CAST ('7.6E+07' AS real), 5, 1, NULL, CAST(0x0000a0d900000000 AS datetime))

GO
SET IDENTITY_INSERT [dbo].[Bill] OFF
GO

--Table dbo.BillDetail

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[BillDetail] (
	[Id] [int] NOT NULL IDENTITY (1, 1),
	[Bill_Id] [int] NOT NULL,
	[ProductPromotion_Id] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
	[GiftPoint] [int] NOT NULL,
	[DateAdded] [date] NOT NULL);
GO

SET IDENTITY_INSERT [dbo].[BillDetail] ON
GO
INSERT INTO [dbo].[BillDetail] ([Id], [Bill_Id], [ProductPromotion_Id], [Quantity], [GiftPoint], [DateAdded])
	VALUES (2, 1, 17, 1, 1, CAST(0x34360b AS date))

GO
SET IDENTITY_INSERT [dbo].[BillDetail] OFF
GO

--Table dbo.Memberships

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Memberships] (
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[Password] [nvarchar](128) NOT NULL,
	[PasswordFormat] [int] NOT NULL,
	[PasswordSalt] [nvarchar](128) NOT NULL,
	[Email] [nvarchar](256) NULL,
	[PasswordQuestion] [nvarchar](256) NULL,
	[PasswordAnswer] [nvarchar](128) NULL,
	[IsApproved] [bit] NOT NULL,
	[IsLockedOut] [bit] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[LastLoginDate] [datetime] NOT NULL,
	[LastPasswordChangedDate] [datetime] NOT NULL,
	[LastLockoutDate] [datetime] NOT NULL,
	[FailedPasswordAttemptCount] [int] NOT NULL,
	[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
	[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
	[FailedPasswordAnswerAttemptWindowsStart] [datetime] NOT NULL,
	[Comment] [nvarchar](256) NULL);
GO

INSERT INTO [dbo].[Memberships] ([ApplicationId], [UserId], [Password], [PasswordFormat], [PasswordSalt], [Email], [PasswordQuestion], [PasswordAnswer], [IsApproved], [IsLockedOut], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [LastLockoutDate], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowsStart], [Comment])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('a2634e86-e7e1-4812-a896-0b8b9f2b3a08' AS uniqueidentifier), N'utnwQ2hsq9gYo8buMGjk6mYfYhyvmtn0TWgZRzHYtYc=', 1, N'W1zz1aZdLAPXARM+W/94Fg==', N'hongphu87902@gmail.com', NULL, NULL, CAST ('True' AS bit), CAST ('False' AS bit), CAST(0x0000a0d900666e54 AS datetime), CAST(0x0000a0e80069454a AS datetime), CAST(0x0000a0d900666e54 AS datetime), CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), NULL)

GO
INSERT INTO [dbo].[Memberships] ([ApplicationId], [UserId], [Password], [PasswordFormat], [PasswordSalt], [Email], [PasswordQuestion], [PasswordAnswer], [IsApproved], [IsLockedOut], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [LastLockoutDate], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowsStart], [Comment])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('9ac55377-e784-47a3-9290-360fb8559932' AS uniqueidentifier), N'+sLt0c9TAZ7pc4GnCcd02siPaROsgZ2RJ64Z4Vz/IsA=', 1, N'/cJp5B+XRJmQpFku8UxsTA==', N'vandai@yahoo.com', NULL, NULL, CAST ('True' AS bit), CAST ('False' AS bit), CAST(0x0000a0d900662524 AS datetime), CAST(0x0000a0d900662524 AS datetime), CAST(0x0000a0d900662524 AS datetime), CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), NULL)

GO
INSERT INTO [dbo].[Memberships] ([ApplicationId], [UserId], [Password], [PasswordFormat], [PasswordSalt], [Email], [PasswordQuestion], [PasswordAnswer], [IsApproved], [IsLockedOut], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [LastLockoutDate], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowsStart], [Comment])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('4c2afb9a-22e0-4d9c-bf1e-4bd6e0df98dd' AS uniqueidentifier), N'j1PYJoxNUfxx4HWvzcwDBbDB/zigXwUOI0cVHXxMrMY=', 1, N'CJaLkcVBHXpsRjsHBwfnDA==', N'mydien2009@gmail.com', NULL, NULL, CAST ('True' AS bit), CAST ('False' AS bit), CAST(0x0000a0d9006607cc AS datetime), CAST(0x0000a0d9006607cc AS datetime), CAST(0x0000a0d9006607cc AS datetime), CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), NULL)

GO
INSERT INTO [dbo].[Memberships] ([ApplicationId], [UserId], [Password], [PasswordFormat], [PasswordSalt], [Email], [PasswordQuestion], [PasswordAnswer], [IsApproved], [IsLockedOut], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [LastLockoutDate], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowsStart], [Comment])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('e5fe5019-56f2-44be-bac3-768d1bfc1db6' AS uniqueidentifier), N'ffHVdXzyfwA8x4+NF2md5sn0mFl4SW/yKmJcrh0pg6k=', 1, N'F5ZX6BR6Y2mAt58XgBMdGA==', N'duyha@gmail.com', NULL, NULL, CAST ('True' AS bit), CAST ('False' AS bit), CAST(0x0000a0d9006652c1 AS datetime), CAST(0x0000a0d9006652c1 AS datetime), CAST(0x0000a0d9006652c1 AS datetime), CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), NULL)

GO
INSERT INTO [dbo].[Memberships] ([ApplicationId], [UserId], [Password], [PasswordFormat], [PasswordSalt], [Email], [PasswordQuestion], [PasswordAnswer], [IsApproved], [IsLockedOut], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [LastLockoutDate], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowsStart], [Comment])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('029ee7ad-6a5b-4920-aee3-9663eddb0d6b' AS uniqueidentifier), N'St3OE0iD0STx2vNRMd2/iEIcOsX2coms8VrCRqlxkr8=', 1, N'HtvPblZRBDyp7ekdMb5LXw==', N'darknight1611@yahoo.com', NULL, NULL, CAST ('True' AS bit), CAST ('False' AS bit), CAST(0x0000a0d90068e4bd AS datetime), CAST(0x0000a0d90068e4bd AS datetime), CAST(0x0000a0d90068e4bd AS datetime), CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), NULL)

GO
INSERT INTO [dbo].[Memberships] ([ApplicationId], [UserId], [Password], [PasswordFormat], [PasswordSalt], [Email], [PasswordQuestion], [PasswordAnswer], [IsApproved], [IsLockedOut], [CreateDate], [LastLoginDate], [LastPasswordChangedDate], [LastLockoutDate], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowsStart], [Comment])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('81a22af2-8911-4870-80a9-cb2bc9737ba2' AS uniqueidentifier), N'dyKs1hTgs1lwi6RuXU4Qm7RPUwBotpIwzWN9XCL0tRY=', 1, N'E6sYoGp5pNA7084nOJx6rg==', N'hongphu8790@gmail.com', NULL, NULL, CAST ('False' AS bit), CAST ('False' AS bit), CAST(0x0000a0d90065b6fd AS datetime), CAST(0x0000a0e80069324b AS datetime), CAST(0x0000a0d90065b6fd AS datetime), CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), 0, CAST(0xffff2fb300000000 AS datetime), NULL)

GO

--Table dbo.Options

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Options] (
	[key] [varchar](50) NOT NULL,
	[value] [varchar](250) NULL);
GO

INSERT INTO [dbo].[Options] ([key], [value])
	VALUES (N'IsDemo', N'true')

GO

--Table dbo.ProductPromotion

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[ProductPromotion] (
	[Id] [int] NOT NULL IDENTITY (1, 1),
	[Name] [nvarchar](50) NULL,
	[GiftPointRequired] [int] NULL,
	[MaxGiftAmount] [int] NULL,
	[RemainAmount] [int] NULL,
	[Quantity] [int] NULL,
	[GiftOfferedCount] [int] NULL,
	[ProductId] [int] NOT NULL);
GO

SET IDENTITY_INSERT [dbo].[ProductPromotion] ON
GO
INSERT INTO [dbo].[ProductPromotion] ([Id], [Name], [GiftPointRequired], [MaxGiftAmount], [RemainAmount], [Quantity], [GiftOfferedCount], [ProductId])
	VALUES (17, N'Chuột vi tính', 1, 3, 68, 100, 0, 1)

GO
INSERT INTO [dbo].[ProductPromotion] ([Id], [Name], [GiftPointRequired], [MaxGiftAmount], [RemainAmount], [Quantity], [GiftOfferedCount], [ProductId])
	VALUES (18, N'USB 8G-AA1', 3, 4, 83, 85, NULL, 1)

GO
INSERT INTO [dbo].[ProductPromotion] ([Id], [Name], [GiftPointRequired], [MaxGiftAmount], [RemainAmount], [Quantity], [GiftOfferedCount], [ProductId])
	VALUES (19, N'USB 8G', 3, 1, 36, 50, 1, 1)

GO
INSERT INTO [dbo].[ProductPromotion] ([Id], [Name], [GiftPointRequired], [MaxGiftAmount], [RemainAmount], [Quantity], [GiftOfferedCount], [ProductId])
	VALUES (22, N'4', 4, 4, 4, 4, NULL, 9)

GO
SET IDENTITY_INSERT [dbo].[ProductPromotion] OFF
GO

--Table dbo.Products

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Products] (
	[Id] [int] NOT NULL IDENTITY (1, 1),
	[Name] [nvarchar](250) NOT NULL,
	[Price] [real] NOT NULL,
	[RemainAmount] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
	[ProductType_Id] [int] NOT NULL,
	[Intro] [text] NULL,
	[Contents] [text] NULL,
	[Image] [nvarchar](250) NULL,
	[GiftPoint] [int] NOT NULL,
	[DateAdded] [datetime] NOT NULL,
	[StartSaleDate] [datetime] NULL,
	[EndSaleDate] [datetime] NULL,
	[StartReceiveDate] [datetime] NULL,
	[EndReceiveDate] [datetime] NULL);
GO

SET IDENTITY_INSERT [dbo].[Products] ON
GO
INSERT INTO [dbo].[Products] ([Id], [Name], [Price], [RemainAmount], [Quantity], [ProductType_Id], [Intro], [Contents], [Image], [GiftPoint], [DateAdded], [StartSaleDate], [EndSaleDate], [StartReceiveDate], [EndReceiveDate])
	VALUES (1, N'Hp G6 – 1323TX', CAST ('1.249E+07' AS real), 10, 20, 1, N'<p><strong>Intel Core i5 2450M(2.50Ghz, 3MB Smart cache, 1333Mhz )</strong></p>
<p><strong>4GB</strong> DDR3</p>
<p><strong>640GB</strong> HDD</p>
<p>DVD-RW</p>
<p>15.6” HD LED&nbsp;</p>
<p><strong><span style="text-decoration: underline;">ATI Radeon HD 7450&nbsp; 1Gb</span></strong></p>', N'<table border="1" cellspacing="0" cellpadding="0" width="576" height="277" class="tb_w">
<tbody>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Processor:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-size: small;"><strong>Intel Core i5 2450M(2.50Ghz, 3MB Smart cache, 1333Mhz )</strong></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Memory:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">4GB DDR3</span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Storage:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;"><strong>640GB</strong> HDD</span></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Optical Drive:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">DVD-RW DL</span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Display:</span></p>
</td>
<td width="396" class="padding">
<p><span style="color: navy;"><span style="font-size: small;"><span style="color: #333333;">15.6" HD LED<br></span></span></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Graphics:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-size: medium;"><strong><span style="text-decoration: underline;">ATI Radeon HD 7450&nbsp; 1Gb</span></strong></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Network:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Wireless 802.11<strong>b/g</strong>/<strong>n</strong>, 10/100Mbps Ethernet</span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Integrated:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Webcam, Micro, 5 in 1 Card reader,Bluetooth</span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Weight:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">2.2Kg with 6Cell Battery</span></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">OS:</span></p>
</td>
<td width="396" class="padding">
<p><strong><span style="color: navy;"><span style="font-size: small;"><span style="background-color: #888888;"><span style="color: #333333;"><span style="background-color: #ffffff;">DOS<br></span></span></span></span></span></strong></p>
</td>
</tr>
</tbody>
</table>', N'1.jpg', 6, CAST(0x0000a0d900000000 AS datetime), CAST(0x0000a0d900107ac0 AS datetime), CAST(0x0000a13400000000 AS datetime), CAST(0x0000a13500000000 AS datetime), CAST(0x0000a15400000000 AS datetime))

GO
INSERT INTO [dbo].[Products] ([Id], [Name], [Price], [RemainAmount], [Quantity], [ProductType_Id], [Intro], [Contents], [Image], [GiftPoint], [DateAdded], [StartSaleDate], [EndSaleDate], [StartReceiveDate], [EndReceiveDate])
	VALUES (2, N'HP pavilion G4 - 1357TX', CAST ('9790000' AS real), 8, 20, 1, N'<p><strong>Intel Core i3 2330M (2.20Ghz, 3MB Smart cache, 1333Mhz )</strong></p>
<p><strong>2GB</strong> DDR3</p>
<p><strong>500GB</strong> HDD</p>
<p>DVD-RW</p>
<p>14.0” HD LED&nbsp;</p>
<p><strong><span style="text-decoration: underline;">ATI Radeon HD 6470&nbsp; 1Gb</span></strong></p>
<p><strong><br></strong></p>', N'<table border="1" cellspacing="0" cellpadding="0" class="tb_w">
<tbody>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Processor:</span></p>
</td>
<td width="396" class="padding">
<p><strong>Intel Core i3 2330M (2.20Ghz, 3MB Smart cache, 1066Mhz )</strong></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Memory:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;"><strong>2</strong>GB DDR3</span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Storage:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;"><strong>500GB</strong> HDD</span></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Optical Drive:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">DVD-RW DL</span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Display:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-size: small;">14.0” HD LED </span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Graphics:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-size: small;"><strong><span style="text-decoration: underline;">ATI Radeon HD 6470&nbsp; 1Gb</span></strong></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Network:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Wireless 802.11<strong>b/g</strong>/<strong>n</strong>, 10/100/1000Mbps Ethernet</span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Integrated:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Webcam, Micro, 5 in 1 Card reader, Bluetooth<br></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">Weight:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">2.2Kg with 6Cell Battery</span></span></p>
</td>
</tr>
<tr>
<td width="115" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">OS:</span></p>
</td>
<td width="396" class="padding">
<p><span style="font-family: Times New Roman; font-size: small;">DOS<strong><br></strong></span></p>
</td>
</tr>
</tbody>
</table>', N'2.jpg', 7, CAST(0x0000a0d900000000 AS datetime), CAST(0x0000a0d9005265c0 AS datetime), CAST(0x0000a13400000000 AS datetime), CAST(0x0000a13500000000 AS datetime), CAST(0x0000a15400000000 AS datetime))

GO
INSERT INTO [dbo].[Products] ([Id], [Name], [Price], [RemainAmount], [Quantity], [ProductType_Id], [Intro], [Contents], [Image], [GiftPoint], [DateAdded], [StartSaleDate], [EndSaleDate], [StartReceiveDate], [EndReceiveDate])
	VALUES (9, N'Lenovo G470 _VGA (RAM 4GB)', CAST ('1.029E+07' AS real), 7, 10, 1, N'<p><strong>Intel Core i3 2330M(2.20Ghz, 3MB Smart cache, 1333Mhz )</strong></p>
<p>4GB DDR3</p>
<p><strong>500GB</strong> HDD</p>
<p>DVD-RW DL</p>
<p>14.0" HD LED</p>
<p><strong><span style="text-decoration: underline;">ATI Radeon HD 6370&nbsp; 1Gb</span></strong></p>', N'<table border="1" cellspacing="0" cellpadding="0" width="659" height="278" class="tb_w">
<tbody>
<tr>
<td width="148" class="padding">
<p>Processor</p>
</td>
<td width="467" class="padding">
<p><strong>Intel Core i3 2330M(2.20Ghz, 3MB Smart cache, 1333Mhz )</strong><strong>&nbsp;</strong></p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>Memory</p>
</td>
<td width="467" class="padding">
<p>4GB DDR3</p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>Storage</p>
</td>
<td width="467" class="padding">
<p><strong>500GB</strong> HDD</p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>Optical Drive</p>
</td>
<td width="467" class="padding">
<p>DVD-RW</p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>Display</p>
</td>
<td width="467" class="padding">
<p>14.0” HD LED</p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>Graphics</p>
</td>
<td width="467" class="padding">
<p><span style="font-size: medium;"><strong><span style="text-decoration: underline;">ATI Radeon HD 6370&nbsp; 1Gb</span></strong></span></p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>Network</p>
</td>
<td width="467" class="padding">
<p>Wireless 802.11 <strong>b/g/n</strong>, 10/100Mbps Ethernet</p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>Integrated</p>
</td>
<td width="467" class="padding">
<p>Webcam, Micro, 5 in 1 Card reader</p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>Weight</p>
</td>
<td width="467" class="padding">
<p>2.2Kg with 6Cell Battery</p>
</td>
</tr>
<tr>
<td width="148" class="padding">
<p>OS</p>
</td>
<td width="467" class="padding">
<p>&nbsp;DOS</p>
</td>
</tr>
</tbody>
</table>', N'9.jpg', 4, CAST(0x0000a10500000000 AS datetime), CAST(0x0000a0e600000000 AS datetime), CAST(0x0000a13200000000 AS datetime), CAST(0x0000a13200000000 AS datetime), CAST(0x0000a13900000000 AS datetime))

GO
INSERT INTO [dbo].[Products] ([Id], [Name], [Price], [RemainAmount], [Quantity], [ProductType_Id], [Intro], [Contents], [Image], [GiftPoint], [DateAdded], [StartSaleDate], [EndSaleDate], [StartReceiveDate], [EndReceiveDate])
	VALUES (14, N'HP G4 – 1209TX', CAST ('1.199E+07' AS real), 3, 50, 1, N'<p><strong>Intel Core i5 2430M(2.3\40Ghz, 3MB Smart cache, 1066Mhz )</strong></p>
<p><strong>4GB</strong> DDR3</p>
<p><strong>500GB</strong> HDD</p>
<p>DVD-RW</p>
<p>14.0” HD LED&nbsp;</p>
<p><strong><span style="text-decoration: underline;">ATI Radeon HD 6470&nbsp; 1Gb</span></strong></p>', N'<table border="1" cellspacing="0" cellpadding="0" class="tb_w">
<tbody>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Processor:</span></span></p>
</td>
<td width="495" class="padding">
<p><strong><span style="font-size: small;"><span style="font-family: Times New Roman;">Intel Core i5 2430M(2.40Ghz, 3MB Smart cache, 1066Mhz )</span></span></strong></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Memory:</span></span></p>
</td>
<td width="495" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">4GB DDR3</span></span></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Storage:</span></span></p>
</td>
<td width="495" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;"><strong>500GB</strong> HDD</span></span></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Optical Drive:</span></span></p>
</td>
<td width="495" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">DVD-RW DL</span></span></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Display:</span></span></p>
</td>
<td width="495" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;"><strong>14” HD LED&nbsp; </strong></span></span></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Graphics:</span></span></p>
</td>
<td width="495" class="padding">
<p><strong><span style="font-family: Times New Roman; font-size: small;">ATI Radeon HD 6470 1GB</span></strong></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Network:</span></span></p>
</td>
<td width="495" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Wireless 802.11<strong>b/g/n</strong>, 10/100Mbps Ethernet</span></span></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Integrated:</span></span></p>
</td>
<td width="495" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Webcam, Micro, 5 in 1 Card reader</span></span></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">Weight:</span></span></p>
</td>
<td width="495" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">2.2Kg with 6Cell Battery</span></span></p>
</td>
</tr>
<tr>
<td width="144" class="padding">
<p><span style="font-size: small;"><span style="font-family: Times New Roman;">OS:</span></span></p>
</td>
<td width="495" class="padding">
<p><span style="font-size: small;">Windows 7 Home Premium</span></p>
</td>
</tr>
</tbody>
</table>', N'14.jpg', 5, CAST(0x0000a10500000000 AS datetime), CAST(0x0000a0e600000000 AS datetime), CAST(0x0000a13200000000 AS datetime), CAST(0x0000a13200000000 AS datetime), CAST(0x0000a13900000000 AS datetime))

GO
INSERT INTO [dbo].[Products] ([Id], [Name], [Price], [RemainAmount], [Quantity], [ProductType_Id], [Intro], [Contents], [Image], [GiftPoint], [DateAdded], [StartSaleDate], [EndSaleDate], [StartReceiveDate], [EndReceiveDate])
	VALUES (39, N'HP Pavilion MS216D (NY670AA)', CAST ('1.2589E+07' AS real), 5, 10, 2, N'<p>
	HP Pavilion All-in-One MS216D (NY670AA)</p>
', N'<center>
	<p>
		<strong>Processor:</strong>AMD Athlon&trade; 64 x2 Dual-Core Processor 3250e(1.5Gnz),</p>
	<p>
		<strong>Chipset:&nbsp; </strong>Chipset AMD M780G</p>
	<p>
		<strong>Memory :</strong> 2GB (DDR2-800)</p>
	<p>
		<strong>Storage:</strong> 320GB SATA-7200rpm/6 in 1 card,</p>
	<p>
		<strong>VGA: </strong>Integrated on motherboard</p>
	<p>
		<strong>Optical drive:</strong> DVDRW LightScribe</p>
	<p>
		<strong>Network:</strong> <strong>: </strong>10/100 Wireless LAN 802.11 b/g mini-card</p>
	<p>
		internal speakers, Web camera Record</p>
	<p>
		<strong>OS :</strong> Free Dos</p>
	<p>
		<strong>KM:</strong> Compaq USB Keyboard&amp;Scroll USB Mouse</p>
	<p>
		<strong>Warranty:</strong>&nbsp; 01 year</p>
	<p>
		&nbsp;LCD 18.5&quot; Wide, Mirror</p>
	<p>
		&nbsp;</p>
</center>
', NULL, 6, CAST(0x0000a10500000000 AS datetime), CAST(0x0000a0e600000000 AS datetime), CAST(0x0000a13200000000 AS datetime), CAST(0x0000a13200000000 AS datetime), CAST(0x0000a13900000000 AS datetime))

GO
INSERT INTO [dbo].[Products] ([Id], [Name], [Price], [RemainAmount], [Quantity], [ProductType_Id], [Intro], [Contents], [Image], [GiftPoint], [DateAdded], [StartSaleDate], [EndSaleDate], [StartReceiveDate], [EndReceiveDate])
	VALUES (40, N'1', CAST ('1' AS real), 1, 1, 1, NULL, NULL, N'undefined', 1, CAST(0x0000a0e90145bbf2 AS datetime), CAST(0x0000a0eb00000000 AS datetime), CAST(0x0000a0f200000000 AS datetime), CAST(0x0000a0ec00000000 AS datetime), CAST(0x0000a0ef00000000 AS datetime))

GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO

--Table dbo.ProductTypes

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[ProductTypes] (
	[Id] [int] NOT NULL IDENTITY (1, 1),
	[Name] [nvarchar](50) NOT NULL);
GO

SET IDENTITY_INSERT [dbo].[ProductTypes] ON
GO
INSERT INTO [dbo].[ProductTypes] ([Id], [Name])
	VALUES (1, N'Laptop - Máy tính xách tay')

GO
INSERT INTO [dbo].[ProductTypes] ([Id], [Name])
	VALUES (2, N'Máy bộ để bàn')

GO
INSERT INTO [dbo].[ProductTypes] ([Id], [Name])
	VALUES (3, N'Phụ kiện máy tính')

GO
INSERT INTO [dbo].[ProductTypes] ([Id], [Name])
	VALUES (4, N'Thiết bị văn phòng')

GO
SET IDENTITY_INSERT [dbo].[ProductTypes] OFF
GO

--Table dbo.Profiles

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Profiles] (
	[UserId] [uniqueidentifier] NOT NULL,
	[PropertyNames] [nvarchar](4000) NOT NULL,
	[PropertyValueStrings] [nvarchar](4000) NOT NULL,
	[PropertyValueBinary] [varbinary](MAX) NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL);
GO

INSERT INTO [dbo].[Profiles] ([UserId], [PropertyNames], [PropertyValueStrings], [PropertyValueBinary], [LastUpdatedDate])
	VALUES (CAST ('a2634e86-e7e1-4812-a896-0b8b9f2b3a08' AS uniqueidentifier), N'Tel:0:-1:Name:0:15:Address:0:-1:', N'Nguyễn Hồng Phú', 0x, CAST(0x0000a0d900666ec4 AS datetime))

GO
INSERT INTO [dbo].[Profiles] ([UserId], [PropertyNames], [PropertyValueStrings], [PropertyValueBinary], [LastUpdatedDate])
	VALUES (CAST ('9ac55377-e784-47a3-9290-360fb8559932' AS uniqueidentifier), N'Tel:0:-1:Name:0:14:Address:0:-1:', N'Nguyễn Văn Đại', 0x, CAST(0x0000a0d9006625b0 AS datetime))

GO
INSERT INTO [dbo].[Profiles] ([UserId], [PropertyNames], [PropertyValueStrings], [PropertyValueBinary], [LastUpdatedDate])
	VALUES (CAST ('4c2afb9a-22e0-4d9c-bf1e-4bd6e0df98dd' AS uniqueidentifier), N'Tel:0:10:Name:10:18:Address:0:-1:', N'0938080790Nguyễn Thị Mỷ Diện', 0x, CAST(0x0000a0d90066083a AS datetime))

GO
INSERT INTO [dbo].[Profiles] ([UserId], [PropertyNames], [PropertyValueStrings], [PropertyValueBinary], [LastUpdatedDate])
	VALUES (CAST ('e5fe5019-56f2-44be-bac3-768d1bfc1db6' AS uniqueidentifier), N'Tel:0:-1:Name:0:13:Address:0:-1:', N'Nguyễn Duy Hà', 0x, CAST(0x0000a0d900665356 AS datetime))

GO
INSERT INTO [dbo].[Profiles] ([UserId], [PropertyNames], [PropertyValueStrings], [PropertyValueBinary], [LastUpdatedDate])
	VALUES (CAST ('029ee7ad-6a5b-4920-aee3-9663eddb0d6b' AS uniqueidentifier), N'Tel:0:-1:Name:0:15:Address:0:-1:', N'Nguyễn Hồng Phú', 0x, CAST(0x0000a0d90068e548 AS datetime))

GO
INSERT INTO [dbo].[Profiles] ([UserId], [PropertyNames], [PropertyValueStrings], [PropertyValueBinary], [LastUpdatedDate])
	VALUES (CAST ('81a22af2-8911-4870-80a9-cb2bc9737ba2' AS uniqueidentifier), N'Tel:0:10:Name:10:15:Address:0:-1:', N'0938080790Nguyễn Hồng Phú', 0x, CAST(0x0000a0d90065be41 AS datetime))

GO

--Table dbo.Resellers

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Resellers] (
	[Id] [int] NOT NULL IDENTITY (1, 1),
	[User_Id] [uniqueidentifier] NULL,
	[Address] [nvarchar](150) NULL,
	[Tel] [varchar](15) NULL,
	[Email] [varchar](50) NULL);
GO

SET IDENTITY_INSERT [dbo].[Resellers] ON
GO
INSERT INTO [dbo].[Resellers] ([Id], [User_Id], [Address], [Tel], [Email])
	VALUES (1, CAST ('029ee7ad-6a5b-4920-aee3-9663eddb0d6b' AS uniqueidentifier), N'Quận 9', N'0938080790', N'hongphu8790@gmail.com')

GO
SET IDENTITY_INSERT [dbo].[Resellers] OFF
GO

--Table dbo.Roles

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Roles] (
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
	[RoleName] [nvarchar](256) NOT NULL,
	[Description] [nvarchar](256) NULL);
GO

INSERT INTO [dbo].[Roles] ([ApplicationId], [RoleId], [RoleName], [Description])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('b7d67b32-5217-4099-8f1a-2f5562701db3' AS uniqueidentifier), N'Reseller', NULL)

GO
INSERT INTO [dbo].[Roles] ([ApplicationId], [RoleId], [RoleName], [Description])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('76102ada-801d-4675-9e9b-44fc6e8a544e' AS uniqueidentifier), N'Customer', NULL)

GO
INSERT INTO [dbo].[Roles] ([ApplicationId], [RoleId], [RoleName], [Description])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('33af1157-e80f-4d7f-811a-6327a5f8efbc' AS uniqueidentifier), N'Quản trị viên', NULL)

GO

--Table dbo.StatusSelling

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[StatusSelling] (
	[Id] [int] NOT NULL IDENTITY (1, 1),
	[Name] [nvarchar](50) NULL);
GO

SET IDENTITY_INSERT [dbo].[StatusSelling] ON
GO
INSERT INTO [dbo].[StatusSelling] ([Id], [Name])
	VALUES (1, N'Đang chọn hàng')

GO
INSERT INTO [dbo].[StatusSelling] ([Id], [Name])
	VALUES (2, N'Đã đặt mua')

GO
INSERT INTO [dbo].[StatusSelling] ([Id], [Name])
	VALUES (3, N'Hoàn tất')

GO
INSERT INTO [dbo].[StatusSelling] ([Id], [Name])
	VALUES (4, N'Nhận hàng')

GO
INSERT INTO [dbo].[StatusSelling] ([Id], [Name])
	VALUES (5, N'Đã nhận hàng')

GO
INSERT INTO [dbo].[StatusSelling] ([Id], [Name])
	VALUES (6, N'Chưa nhận hàng')

GO
SET IDENTITY_INSERT [dbo].[StatusSelling] OFF
GO

--Table dbo.Users

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[Users] (
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[IsAnonymous] [bit] NOT NULL,
	[LastActivityDate] [datetime] NOT NULL);
GO

INSERT INTO [dbo].[Users] ([ApplicationId], [UserId], [UserName], [IsAnonymous], [LastActivityDate])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('a2634e86-e7e1-4812-a896-0b8b9f2b3a08' AS uniqueidentifier), N'hongphu', CAST ('False' AS bit), CAST(0x0000a0e80069454a AS datetime))

GO
INSERT INTO [dbo].[Users] ([ApplicationId], [UserId], [UserName], [IsAnonymous], [LastActivityDate])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('9ac55377-e784-47a3-9290-360fb8559932' AS uniqueidentifier), N'vandai', CAST ('False' AS bit), CAST(0x0000a0d9006625b0 AS datetime))

GO
INSERT INTO [dbo].[Users] ([ApplicationId], [UserId], [UserName], [IsAnonymous], [LastActivityDate])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('4c2afb9a-22e0-4d9c-bf1e-4bd6e0df98dd' AS uniqueidentifier), N'mydien', CAST ('False' AS bit), CAST(0x0000a0d90066083a AS datetime))

GO
INSERT INTO [dbo].[Users] ([ApplicationId], [UserId], [UserName], [IsAnonymous], [LastActivityDate])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('e5fe5019-56f2-44be-bac3-768d1bfc1db6' AS uniqueidentifier), N'duyha', CAST ('False' AS bit), CAST(0x0000a0d900665356 AS datetime))

GO
INSERT INTO [dbo].[Users] ([ApplicationId], [UserId], [UserName], [IsAnonymous], [LastActivityDate])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('029ee7ad-6a5b-4920-aee3-9663eddb0d6b' AS uniqueidentifier), N'daily1', CAST ('False' AS bit), CAST(0x0000a0d90068e548 AS datetime))

GO
INSERT INTO [dbo].[Users] ([ApplicationId], [UserId], [UserName], [IsAnonymous], [LastActivityDate])
	VALUES (CAST ('85ba74d8-71c5-484d-a5b9-0c82cbb57c8d' AS uniqueidentifier), CAST ('81a22af2-8911-4870-80a9-cb2bc9737ba2' AS uniqueidentifier), N'admin', CAST ('False' AS bit), CAST(0x0000a0e80069324b AS datetime))

GO

--Table dbo.UsersInRoles

USE [SuperStar_Nhom11]
GO

--Create table and its columns
CREATE TABLE [dbo].[UsersInRoles] (
	[Roles_RoleId] [uniqueidentifier] NOT NULL,
	[Users_UserId] [uniqueidentifier] NOT NULL);
GO

--Executing Entities
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE	 proc [dbo].[spUpdateQuatilyProduct]
@Id int,
@Quantily int
as
	begin
	declare @QuantilyNow int
	declare @RemainAmount int

	select @QuantilyNow = Quantity,@RemainAmount = RemainAmount
	from Products
	where Id = @Id

	if(@QuantilyNow > @Quantily)
		begin
		raiserror (N'Chỉ được tăng số lượng<Số lượng cập nhật phải lớn hơn số lượng hiện tại>!',1,16)
		rollback Tran
		end
	else
		UPDATE    Products
		SET       RemainAmount = @RemainAmount + @Quantily - @QuantilyNow, Quantity = @Quantily
		WHERE Id = @Id
	end
	
---------------------------------------------------------------------------
-- SV: 1142020 ------------------------------------------------------------
-- update số lượng tặng phẩm muốn đổi  của một chi tiết đơn hàng(BillDetail)
---------------------------------------------------------------------------


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[Case02_1a_spDeleteBill]
@Id int -- mã hóa đơn
AS	
BEGIN TRAN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- dirty read
	--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --dead lock
	BEGIN
		DECLARE @RemainAmount_Pro int, @Product_Id int , 
			@Quantity_Delete int, @Promotion_Id int, @Quantity int, 
			@Status_Id int
		
		SELECT @RemainAmount_Pro = p.RemainAmount, 
				@Product_Id = b.Product_Id, 
				@Quantity_Delete = b.Quantity, @Status_Id = b.Status_Id 
		FROM Products p, Bill b
		WHERE b.Id = @Id and p.Id = b.Product_Id

		IF(@Status_Id in (SELECT Id FROM StatusSelling WHERE Name like '%Hủy%'))
			ROLLBACK TRAN
		ELSE	
			BEGIN
				UPDATE    Bill
				SET       Status_Id = 7
				WHERE	ID = @Id

				UPDATE    Products
				SET       RemainAmount = @RemainAmount_Pro + @Quantity_Delete
				WHERE	Id = @Product_Id

				DECLARE curListDetailBillNeedDelete CURSOR
				FOR SELECT Id, Quantity FROM BillDetail
							WHERE Bill_Id = @Id
				OPEN curListDetailBillNeedDelete
				FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
				while @@FETCH_STATUS = 0
					BEGIN 
						DECLARE @RemainAmount int
						SELECT @RemainAmount = RemainAmount
						FROM ProductPromotion 
						WHERE Id = @Promotion_Id

						UPDATE    ProductPromotion
						SET       RemainAmount = @RemainAmount + @Quantity
						WHERE Id = @Promotion_Id

						DELETE FROM BillDetail WHERE Bill_Id = @Id and Id = @Promotion_Id
						FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
					END
				CLOSE curListDetailBillNeedDelete
				DEALLOCATE curListDetailBillNeedDelete
			END	
			WAITFOR DELAY '00:00:10'	
	ROLLBACK
END


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[Case02_1b_spDeleteBill]
@Id int -- mã hóa đơn
AS	
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --lost update
	--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- solve
	BEGIN TRAN
		DECLARE @RemainAmount_Pro int, @Product_Id int , @Quantity_Delete int, @Promotion_Id int, @Quantity int, @Status_Id int
		
		SELECT @RemainAmount_Pro = p.RemainAmount, @Product_Id = b.Product_Id, 
				@Quantity_Delete = b.Quantity, @Status_Id = b.Status_Id 
		FROM Products p, Bill b
		WHERE b.Id = @Id and p.Id = b.Product_Id

		IF(@Status_Id in (SELECT Id FROM StatusSelling WHERE Name like '%Hủy%'))
			ROLLBACK TRAN
		ELSE	
			BEGIN
				UPDATE    Bill
				SET       Status_Id = 7
				WHERE	ID = @Id

				UPDATE    Products
				SET       RemainAmount = @RemainAmount_Pro + @Quantity_Delete
				WHERE	Id = @Product_Id

				DECLARE curListDetailBillNeedDelete CURSOR
				FOR SELECT Id, Quantity FROM BillDetail
							WHERE Bill_Id = @Id
				OPEN curListDetailBillNeedDelete
				FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
				while @@FETCH_STATUS = 0
					BEGIN 
						DECLARE @RemainAmount int
						SELECT @RemainAmount = RemainAmount
						FROM ProductPromotion 
						WHERE Id = @Promotion_Id

						UPDATE    ProductPromotion
						SET       RemainAmount = @RemainAmount + @Quantity
						WHERE Id = @Promotion_Id

						DELETE FROM BillDetail WHERE Bill_Id = @Id and Id = @Promotion_Id
						FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
					END
				CLOSE curListDetailBillNeedDelete
				DEALLOCATE curListDetailBillNeedDelete
			END		
	COMMIT TRAN
END


--------------------------------------------------------
-- SV: 1142014 -----------------------------------------
-- 7.Case 02 Hủy hóa đơn
--------------------------------------------------------
IF OBJECT_ID('dbo.Case02_2a_spDeleteBill') IS NOT NULL
 DROP PROC dbo.Case02_2a_spDeleteBill


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[Case03_1b2_spDeleteBill]
@Id int -- mã hóa đơn
AS	
BEGIN TRAN	
	BEGIN 
		DECLARE @RemainAmount_Pro int, @Product_Id int , 
				@Quantity_Delete int, @Promotion_Id int, @Quantity int, 
				@Status_Id int
		
		SELECT @RemainAmount_Pro = p.RemainAmount, @Product_Id = b.Product_Id, 
				@Quantity_Delete = b.Quantity, @Status_Id = b.Status_Id 
		FROM Products p, Bill b
		WHERE b.Id = @Id and p.Id = b.Product_Id

		IF(@Status_Id in (SELECT Id FROM StatusSelling WHERE Name like '%Hủy%'))
			ROLLBACK TRAN
		
		ELSE	
			BEGIN
				UPDATE    Bill
				SET       Status_Id = 7
				WHERE	ID = @Id

				UPDATE    Products
				SET       RemainAmount = @RemainAmount_Pro + @Quantity_Delete
				WHERE	Id = @Product_Id
							
		WAITFOR DELAY '00:00:05'
		ROLLBACK TRAN

				DECLARE curListDetailBillNeedDelete CURSOR
				FOR SELECT Id, Quantity FROM BillDetail
							WHERE Bill_Id = @Id
				OPEN curListDetailBillNeedDelete
				FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
				while @@FETCH_STATUS = 0
					BEGIN 
						DECLARE @RemainAmount int
						SELECT @RemainAmount = RemainAmount
						FROM ProductPromotion 
						WHERE Id = @Promotion_Id

						UPDATE    ProductPromotion
						SET       RemainAmount = @RemainAmount + @Quantity
						WHERE Id = @Promotion_Id

						DELETE FROM BillDetail WHERE Bill_Id = @Id and Id = @Promotion_Id
						FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
					END
				CLOSE curListDetailBillNeedDelete
				DEALLOCATE curListDetailBillNeedDelete
			END		
	END


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[Case03_2b_spDeleteBill]
@Id int -- mã hóa đơn
AS	
BEGIN TRAN	
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --lost update
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --solve
	BEGIN 
		DECLARE @RemainAmount_Pro int, @Product_Id int , 
				@Quantity_Delete int, @Promotion_Id int, @Quantity int, 
				@Status_Id int
		
		SELECT @RemainAmount_Pro = p.RemainAmount, 
				@Product_Id = b.Product_Id, 
				@Quantity_Delete = b.Quantity, @Status_Id = b.Status_Id 
		FROM Products p, Bill b
		WHERE b.Id = @Id and p.Id = b.Product_Id

		WAITFOR DELAY '00:00:05'

		IF(@Status_Id in (SELECT Id FROM StatusSelling WHERE Name like '%Hủy%'))
			ROLLBACK TRAN
		
		ELSE	
			BEGIN
				UPDATE    Bill
				SET       Status_Id = 7
				WHERE	ID = @Id

				UPDATE    Products
				SET       RemainAmount = @RemainAmount_Pro + @Quantity_Delete
				WHERE	Id = @Product_Id

				DECLARE curListDetailBillNeedDelete CURSOR
				FOR SELECT Id, Quantity FROM BillDetail
							WHERE Bill_Id = @Id
				OPEN curListDetailBillNeedDelete
				FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
				while @@FETCH_STATUS = 0
					BEGIN 
						DECLARE @RemainAmount int
						SELECT @RemainAmount = RemainAmount
						FROM ProductPromotion 
						WHERE Id = @Promotion_Id

						UPDATE    ProductPromotion
						SET       RemainAmount = @RemainAmount + @Quantity
						WHERE Id = @Promotion_Id

						DELETE FROM BillDetail WHERE Bill_Id = @Id and Id = @Promotion_Id
						FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
					END
				CLOSE curListDetailBillNeedDelete
				DEALLOCATE curListDetailBillNeedDelete
			END
			WAITFOR DELAY '00:00:05'		
	END
COMMIT TRAN


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[spProduct_GetHotProducts]
@count int
as 
	BEGIN

		SELECT top (@count) pp.* FROM
		(
			SELECT count(*) as SL, b.Product_Id
			FROM Bill b LEFT JOIN Products p ON b.Product_Id = p.Id 
			WHERE GETDATE() between p.StartSaleDate  and p.EndSaleDate
			Group By b.Product_Id
		) a, Products pp 
		WHERE a.Product_Id = pp.id	
		ORDER BY a.SL DESC

	END

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[spProduct_GetNewProducts]
@count int
as 
	BEGIN
			SELECT top(@count) p.*
			FROM Products p 
			WHERE GETDATE() between p.StartSaleDate and p.EndSaleDate
			ORDER BY p.StartSaleDate DESC
	END

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[CASE15_1a_spDeleteBillDetail_Promotions]
@BillDetail_Id int,
@ProductPromotion_Id int
as
	BEGIN TRAN
		SET TRAN ISOLATION LEVEL READ UNCOMMITTED
		-- Đọc số lượng tồn sp khuyến mãi trong kho 
		DECLARE @remainAmount_ProductPromotion int
		SELECT @remainAmount_ProductPromotion = RemainAmount
		FROM ProductPromotion 
		WHERE Id = @ProductPromotion_Id
		-- Đọc số lượng chọn sp khuyến mãi đã chọn trước đó
		DECLARE @quantity_BillDetails int
		SELECT @quantity_BillDetails = Quantity
		FROM BillDetail_Promotions
		WHERE BillDetail_Id = @BillDetail_Id AND ProductPromotion_Id = @ProductPromotion_Id

		-- Xóa sp khuyến mãi đã đặt
		DELETE FROM BillDetail_Promotions
		WHERE BillDetail_Id = @BillDetail_Id AND ProductPromotion_Id = @ProductPromotion_Id
		-- Cập nhật lại số lượng sp khuyến mãi tồn
		print @remainAmount_ProductPromotion
	--	UPDATE ProductPromotion 
	--	SET RemainAmount = @remainAmount_ProductPromotion + @quantity_BillDetails
	--	WHERE Id =	@ProductPromotion_Id
	COMMIT TRAN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[CASE15_1a_spUpdateProductPromotion]
@Name nvarchar,
@Id int,
@GiftPointRequired int,
@Quantity int,
@MaxGiftAmount int
as
	BEGIN TRAN

		DECLARE @old_Quantity int, @old_RemainAmount int, @old_MaxGiftAmount int, @RemainAmount int
		SELECT @old_Quantity = Quantity, @old_RemainAmount = RemainAmount, @old_MaxGiftAmount = MaxGiftAmount
		FROM ProductPromotion WHERE Id = @Id
		SET @RemainAmount =  @Quantity - @old_Quantity;

		-- Kiểm tra tổng số lượng mới có ít hơn tổng số lượng cũ
		IF(@Quantity < @old_Quantity)
			BEGIN
				raiserror(N'Không thể cập nhật số lượng lúc sau nhỏ hơn lúc đầu',16,1)
				ROLLBACK TRAN
			END
		ELSE
			BEGIN
				UPDATE ProductPromotion
				SET Quantity = @Quantity
				WHERE Id = @Id
			END
		select * from ProductPromotion
		WAITFOR DELAY '00:00:05'

		-- Cập nhật số tặng phẩm nếu <= số lượng tồn
		IF(@MaxGiftAmount > @RemainAmount)
			BEGIN
				raiserror(N'Số lượng tặng phẩm tối đa phải nhỏ hơn số lượng tồn',16,1)
				ROLLBACK TRAN
			END
		
		-- Cập nhật số điểm yêu cầu nếu số điểm mới <= số điểm tối đa của sp chính
		DECLARE @GiftPoint_Product int
		SELECT @GiftPoint_Product = pd.GiftPoint
		FROM ProductPromotion pp, Products pd
		WHERE pp.ProductId = pd.Id AND pp.Id = @Id
		
		IF(@GiftPointRequired > @GiftPoint_Product)
			BEGIN
				raiserror(N'Điểm tặng phẩm phải nhỏ hơn hoặc bằng điểm tặng của sản phẩm bán',16,1)
				ROLLBACK TRAN
			END
		-- Cập nhật
		UPDATE ProductPromotion
			SET Name = @Name,
				MaxGiftAmount = @MaxGiftAmount, 
				RemainAmount =  @old_RemainAmount + @RemainAmount,
				Quantity = @Quantity
		WHERE Id = @Id
	COMMIT TRAN


exec CASE15_1a_spUpdateProductPromotion
@Name = 'USB 4G',
@Id = 18,
@GiftPointRequired = 1,
@Quantity = 101,
@MaxGiftAmount = 70

select * from ProductPromotion

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[CASE15_1b_spDeleteBillDetail_Promotions]
@BillDetail_Id int,
@ProductPromotion_Id int
as
	BEGIN TRAN
		SET TRAN ISOLATION LEVEL READ UNCOMMITTED
		-- Đọc số lượng tồn sp khuyến mãi trong kho 
		DECLARE @remainAmount_ProductPromotion int
		SELECT @remainAmount_ProductPromotion = RemainAmount
		FROM ProductPromotion 
		WHERE Id = @ProductPromotion_Id
		-- Đọc số lượng chọn sp khuyến mãi đã chọn trước đó
		DECLARE @quantity_BillDetails int
		SELECT @quantity_BillDetails = Quantity
		FROM BillDetail_Promotions
		WHERE BillDetail_Id = @BillDetail_Id AND ProductPromotion_Id = @ProductPromotion_Id

		-- Xóa sp khuyến mãi đã đặt
		DELETE FROM BillDetail_Promotions
		WHERE BillDetail_Id = @BillDetail_Id AND ProductPromotion_Id = @ProductPromotion_Id
		-- Cập nhật lại số lượng sp khuyến mãi tồn
		print @remainAmount_ProductPromotion
	--	UPDATE ProductPromotion 
	--	SET RemainAmount = @remainAmount_ProductPromotion + @quantity_BillDetails
	--	WHERE Id =	@ProductPromotion_Id
	COMMIT TRAN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[spInsertDetailBill]
@Bill_Id int,
@ProductPromotion_Id int,
@Quantity int
as
	BEGIN TRAN
		
		DECLARE @RemainAmount int,@MaxGiftAmount int, @GiftPointRequired int
		-- Đọc số tặng phẩm tồn, tặng phẩm tối đa, điểm yêu cầu
		SELECT @RemainAmount = RemainAmount,
			   @MaxGiftAmount = MaxGiftAmount,
			   @GiftPointRequired = GiftPointRequired
		FROM ProductPromotion
		WHERE Id = @ProductPromotion_Id
		-- Đọc số điểm thưởng
		DECLARE @GiftPoint int
		SELECT @GiftPoint = GiftPoint
		FROM Bill 
		WHERE Id = @Bill_Id 
		-- Nếu thỏa điều kiện nhận tặng phẩm
		IF(@Quantity <= @RemainAmount AND @Quantity <= @MaxGiftAmount AND @GiftPoint >= @GiftPointRequired)
			BEGIN
				-- Thêm chi tiết tặng pham73
				INSERT INTO BillDetail(Bill_Id,GiftPoint,ProductPromotion_Id,Quantity,DateAdded)
				VALUES (@Bill_Id,@GiftPoint,@ProductPromotion_Id,@Quantity,GETDATE())
				-- Cập nhật số lượng tồn tặng phẩm
				UPDATE ProductPromotion 
				SET RemainAmount = RemainAmount - @Quantity
			END
		ELSE
			BEGIN
				raiserror(N'Không thể cập nhật số lượng lúc sau nhỏ hơn lúc đầu',16,1)
				return -1
			END

	COMMIT TRAN


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[Case02_2a_spDeleteBill]
@Id int -- mã hóa đơn
AS	
BEGIN TRAN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --lost update
	--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --dead lock
	BEGIN 
		DECLARE @RemainAmount_Pro int, @Product_Id int , 
				@Quantity_Delete int, @Promotion_Id int, @Quantity int, 
				@Status_Id int
		
		SELECT @RemainAmount_Pro = p.RemainAmount, @Product_Id = b.Product_Id, 
				@Quantity_Delete = b.Quantity, @Status_Id = b.Status_Id 
		FROM Products p, Bill b
		WHERE b.Id = @Id and p.Id = b.Product_Id
		
		WAITFOR DELAY '00:00:05'

		IF(@Status_Id in (SELECT Id FROM StatusSelling WHERE Name like '%Hủy%'))
			ROLLBACK TRAN
		
		ELSE	
			BEGIN
				UPDATE    Bill
				SET       Status_Id = 7
				WHERE	ID = @Id

				UPDATE    Products
				SET       RemainAmount = @RemainAmount_Pro + @Quantity_Delete
				WHERE	Id = @Product_Id

				DECLARE curListDetailBillNeedDelete CURSOR
				FOR SELECT Id, Quantity FROM BillDetail
							WHERE Bill_Id = @Id
				OPEN curListDetailBillNeedDelete
				FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
				while @@FETCH_STATUS = 0
					BEGIN 
						DECLARE @RemainAmount int
						SELECT @RemainAmount = RemainAmount
						FROM ProductPromotion 
						WHERE Id = @Promotion_Id
						
					WAITFOR DELAY '00:00:05'

						UPDATE    ProductPromotion
						SET       RemainAmount = @RemainAmount + @Quantity
						WHERE Id = @Promotion_Id

						DELETE FROM BillDetail WHERE Bill_Id = @Id and Id = @Promotion_Id
						FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
					END
				CLOSE curListDetailBillNeedDelete
				DEALLOCATE curListDetailBillNeedDelete
			END		
	END
COMMIT TRAN

--------------------------------------------------------
-- SV: 1142014 -----------------------------------------
-- 8.Case 02_2b Hủy hóa đơn
--------------------------------------------------------
IF OBJECT_ID('dbo.Case02_2b_spDeleteBill') IS NOT NULL
 DROP PROC dbo.Case02_2b_spDeleteBill


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[Case02_2b_spDeleteBill]
@Id int -- mã hóa đơn
AS	
BEGIN TRAN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --lost update
	--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --dead lock
	BEGIN 
		DECLARE @RemainAmount_Pro int, @Product_Id int , 
				@Quantity_Delete int, @Promotion_Id int, @Quantity int,
				@Status_Id int
		
		SELECT @RemainAmount_Pro = p.RemainAmount, @Product_Id = b.Product_Id, 
				@Quantity_Delete = b.Quantity, @Status_Id = b.Status_Id 
		FROM Products p, Bill b
		WHERE b.Id = @Id and p.Id = b.Product_Id
		
		WAITFOR DELAY '00:00:05'

		IF(@Status_Id in (SELECT Id FROM StatusSelling WHERE Name like '%Hủy%'))
			ROLLBACK TRAN
		
		ELSE	
			BEGIN
				UPDATE    Bill
				SET       Status_Id = 7
				WHERE	ID = @Id

				UPDATE    Products
				SET       RemainAmount = @RemainAmount_Pro + @Quantity_Delete
				WHERE	Id = @Product_Id

				DECLARE curListDetailBillNeedDelete CURSOR
				FOR SELECT Id, Quantity FROM BillDetail
							WHERE Bill_Id = @Id
				OPEN curListDetailBillNeedDelete
				FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
				while @@FETCH_STATUS = 0
					BEGIN 
						DECLARE @RemainAmount int
						SELECT @RemainAmount = RemainAmount
						FROM ProductPromotion 
						WHERE Id = @Promotion_Id
						
					WAITFOR DELAY '00:00:05'

						UPDATE    ProductPromotion
						SET       RemainAmount = @RemainAmount + @Quantity
						WHERE Id = @Promotion_Id

						DELETE FROM BillDetail WHERE Bill_Id = @Id and Id = @Promotion_Id
						FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
					END
				CLOSE curListDetailBillNeedDelete
				DEALLOCATE curListDetailBillNeedDelete
			END		
	END
COMMIT TRAN


--------------------------------------------------------
-- SV: 1142014 -----------------------------------------
-- 9.Case 03_1b1 Hủy hóa đơn
--------------------------------------------------------
IF OBJECT_ID('dbo.Case03_1b1_spDeleteBill') IS NOT NULL
 DROP PROC dbo.Case03_1b1_spDeleteBill


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[Case03_1b1_spDeleteBill]
@Id int -- mã hóa đơn
AS	
BEGIN TRAN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --lost update
	--SET TRANSACTION ISOLATION LEVEL READ COMMITTED --solve
	BEGIN 
		DECLARE @RemainAmount_Pro int, @Product_Id int ,
				@Quantity_Delete int, @Promotion_Id int, @Quantity int, 
				@Status_Id int
		
		SELECT @RemainAmount_Pro = p.RemainAmount, @Product_Id = b.Product_Id, 
				@Quantity_Delete = b.Quantity, @Status_Id = b.Status_Id 
		FROM Products p, Bill b
		WHERE b.Id = @Id and p.Id = b.Product_Id

		IF(@Status_Id in (SELECT Id FROM StatusSelling WHERE Name like '%Hủy%'))
			ROLLBACK TRAN
		
		ELSE	
			BEGIN
				UPDATE    Bill
				SET       Status_Id = 7
				WHERE	ID = @Id

				UPDATE    Products
				SET       RemainAmount = @RemainAmount_Pro + @Quantity_Delete
				WHERE	Id = @Product_Id

				DECLARE curListDetailBillNeedDelete CURSOR
				FOR SELECT Id, Quantity FROM BillDetail
							WHERE Bill_Id = @Id
				OPEN curListDetailBillNeedDelete
				FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
				while @@FETCH_STATUS = 0
					BEGIN 
						DECLARE @RemainAmount int
						SELECT @RemainAmount = RemainAmount
						FROM ProductPromotion 
						WHERE Id = @Promotion_Id

						UPDATE    ProductPromotion
						SET       RemainAmount = @RemainAmount + @Quantity
						WHERE Id = @Promotion_Id

						DELETE FROM BillDetail WHERE Bill_Id = @Id and Id = @Promotion_Id
						FETCH NEXT FROM curListDetailBillNeedDelete INTO @Id, @Quantity
					END
				CLOSE curListDetailBillNeedDelete
				DEALLOCATE curListDetailBillNeedDelete
			END		
	END
COMMIT TRAN

--------------------------------------------------------
-- SV: 1142014 -----------------------------------------
-- 10.Case 03_1b2 Hủy hóa đơn
--------------------------------------------------------
IF OBJECT_ID('dbo.Case03_1b2_spDeleteBill') IS NOT NULL
 DROP PROC dbo.Case03_1b2_spDeleteBill


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[spProductPromotion_Update] 
    @Id int,
    @Name nvarchar(50),
    @GiftPointRequired int,
    @MaxGiftAmount int,
    @RemainAmount int,
    @Quantity int,
    @GiftOfferedCount int
AS 
	BEGIN TRAN

	UPDATE [dbo].[ProductPromotion]
	SET    [Name] = @Name, [GiftPointRequired] = @GiftPointRequired, [MaxGiftAmount] = @MaxGiftAmount, [RemainAmount] = @RemainAmount, [Quantity] = @Quantity, [GiftOfferedCount] = @GiftOfferedCount
	WHERE  [Id] = @Id
	COMMIT
--------------------------------------------------------
-- Delete [ProductPromotion] 
--------------------------------------------------------

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[spProductPromotion_Delete] 
    @Id int
AS 
	BEGIN TRAN
	DELETE FROM  [dbo].[BillDetail] WHERE ProductPromotion_Id = @Id 
	DELETE FROM   [dbo].[ProductPromotion] WHERE  [Id] = @Id

	COMMIT

-- SV: 1142066 -----------------------------------------
-- Thêm sản phẩm
--------------------------------------------------------

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[spProduct_Insert] 
    @Name nvarchar(250),
    @Price real,
    @RemainAmount int,
    @Quantity int,
    @ProductType_Id int,
    @Intro text,
    @Contents text,
    @Image nvarchar(250),
    @GiftPoint int,
    @DateAdded datetime,
    @StartSaleDate datetime,
    @EndSaleDate datetime,
    @StartReceiveDate datetime,
    @EndReceiveDate datetime
AS 
	BEGIN TRAN
	
	INSERT INTO [dbo].[Products] ([Name], [Price], [RemainAmount], [Quantity], [ProductType_Id], [Intro], [Contents], [Image], [GiftPoint], [DateAdded], [StartSaleDate], [EndSaleDate], [StartReceiveDate], [EndReceiveDate])
	SELECT @Name, @Price, @RemainAmount, @Quantity, @ProductType_Id, @Intro, @Contents, @Image, @GiftPoint, GetDate(), @StartSaleDate, @EndSaleDate, @StartReceiveDate, @EndReceiveDate
    SELECT * FROM Products WHERE Id = SCOPE_IDENTITY()           
	COMMIT

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[spProduct_Update] 
    @Id int,
    @Name nvarchar(250),
    @Price real,
    @RemainAmount int,
    @Quantity int,
    @ProductType_Id int,
    @Intro text,
    @Contents text,
    @Image nvarchar(250),
    @GiftPoint int,
    @StartSaleDate datetime,
    @EndSaleDate datetime,
    @StartReceiveDate datetime,
    @EndReceiveDate datetime
AS 
	BEGIN TRAN

	UPDATE [dbo].[Products]
	SET    [Name] = @Name, [Price] = @Price, [RemainAmount] = @RemainAmount, [Quantity] = @Quantity, [ProductType_Id] = @ProductType_Id, [Intro] = @Intro, [Contents] = @Contents, [Image] = @Image, [GiftPoint] = @GiftPoint,  [StartSaleDate] = @StartSaleDate, [EndSaleDate] = @EndSaleDate, [StartReceiveDate] = @StartReceiveDate, [EndReceiveDate] = @EndReceiveDate
	WHERE  [Id] = @Id
	COMMIT

--------------------------------------------------------
-- Delete [Products] 
--------------------------------------------------------

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[spProducts_Delete] 
    @Id int
AS 
	BEGIN TRAN
	
	DELETE FROM  [dbo].[BillDetail] WHERE ProductPromotion_Id in (SELECT Id FROM [ProductPromotion] WHERE ProductId = @Id)
	DELETE FROM  [dbo].[ProductPromotion] WHERE  ProductId = @Id
	DELETE FROM  [dbo].[Bill] WHERE  [Product_Id] = @Id
	DELETE FROM  [dbo].[Products] WHERE  [Id] = @Id

	COMMIT
	
	
-- SV: 1142066 -----------------------------------------
-- Cập nhật sản phẩm khuyến mãi (ProductPromotion)
--------------------------------------------------------

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[spUpdateProductPromotion]
@Name nvarchar,
@Id int,
@GiftPointRequired int,
@Quantity int,
@MaxGiftAmount int
as
	BEGIN TRAN

		DECLARE @old_Quantity int, @old_RemainAmount int, @old_MaxGiftAmount int, @RemainAmount int
		SELECT @old_Quantity = Quantity, @old_RemainAmount = RemainAmount, @old_MaxGiftAmount = MaxGiftAmount
		FROM ProductPromotion WHERE Id = @Id
		SET @RemainAmount =  @Quantity - @old_Quantity;

		-- Kiểm tra tổng số lượng mới có ít hơn tổng số lượng cũ
		IF(@Quantity < @old_Quantity)
			BEGIN
				raiserror(N'Không thể cập nhật số lượng lúc sau nhỏ hơn lúc đầu',16,1)
				ROLLBACK TRAN
			END

		-- Cập nhật số tặng phẩm nếu <= số lượng tồn
		IF(@MaxGiftAmount > @RemainAmount)
			BEGIN
				raiserror(N'Số lượng tặng phẩm tối đa phải nhỏ hơn số lượng tồn',16,1)
				ROLLBACK TRAN
			END

		-- Cập nhật số điểm yêu cầu nếu số điểm mới <= số điểm tối đa của sp chính
		DECLARE @GiftPoint_Product int
		SELECT @GiftPoint_Product = pd.GiftPoint
		FROM ProductPromotion pp, Products pd
		WHERE pp.ProductId = pd.Id AND pp.Id = @Id
		
		IF(@GiftPointRequired > @GiftPoint_Product)
			BEGIN
				raiserror(N'Điểm tặng phẩm phải nhỏ hơn hoặc bằng điểm tặng của sản phẩm bán',16,1)
				ROLLBACK TRAN
			END
		-- Cập nhật
		UPDATE ProductPromotion
			SET Name = @Name,
				MaxGiftAmount = @MaxGiftAmount, 
				RemainAmount =  @old_RemainAmount + @RemainAmount,
				Quantity = @Quantity
		WHERE Id = @Id
	COMMIT TRAN

-- SV: 1142066 ------------------------------------------------------------
-- Xóa 1 sản phẩm khuyến mãi trên đơn đặt hàng (BillDetail_Promotions)
---------------------------------------------------------------------------

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[spDeleteBillDetail]
@Bill_Id int,
@ProductPromotion_Id int
as
	BEGIN TRAN
		-- Đọc số lượng tồn sp khuyến mãi trong kho 
		DECLARE @remainAmount_ProductPromotion int
		SELECT @remainAmount_ProductPromotion = RemainAmount
		FROM ProductPromotion 
		WHERE Id = @ProductPromotion_Id
		-- Đọc số lượng chọn sp khuyến mãi đã chọn trước đó
		DECLARE @quantity_BillDetails int
		SELECT @quantity_BillDetails = Quantity
		FROM BillDetail
		WHERE Bill_Id = @Bill_Id AND ProductPromotion_Id = @ProductPromotion_Id

		-- Xóa sp khuyến mãi đã đặt
		DELETE FROM BillDetail
		WHERE Bill_Id = @Bill_Id AND ProductPromotion_Id = @ProductPromotion_Id
		-- Cập nhật lại số lượng sp khuyến mãi tồn
		print @remainAmount_ProductPromotion
		UPDATE ProductPromotion 
		SET RemainAmount = @remainAmount_ProductPromotion + @quantity_BillDetails
		WHERE Id =	@ProductPromotion_Id
	COMMIT TRAN
	
-- SV: 1142066 -----------------------------------------
-- Thêm tặng phẩm vào chi tiết hóa đơn (BillDetail)
--------------------------------------------------------

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[spInsertBillDetail]
@Bill_Id int,
@ProductPromotion_Id int,
@Quantity int
as
	BEGIN TRAN
		
		DECLARE @RemainAmount int,@MaxGiftAmount int, @GiftPointRequired int
		-- Đọc số tặng phẩm tồn, tặng phẩm tối đa, điểm yêu cầu
		SELECT @RemainAmount = RemainAmount,
			   @MaxGiftAmount = MaxGiftAmount,
			   @GiftPointRequired = GiftPointRequired
		FROM ProductPromotion
		WHERE Id = @ProductPromotion_Id
		-- Đọc số điểm thưởng
		DECLARE @GiftPoint int
		SELECT @GiftPoint = GiftPoint
		FROM Bill 
		WHERE Id = @Bill_Id 
		-- Nếu thỏa điều kiện nhận tặng phẩm
		IF(@Quantity <= @RemainAmount AND @Quantity <= @MaxGiftAmount AND @GiftPoint >= @GiftPointRequired)
			BEGIN
				-- Thêm chi tiết tặng pham73
				INSERT INTO BillDetail(Bill_Id,GiftPoint,ProductPromotion_Id,Quantity,DateAdded)
				VALUES (@Bill_Id,@GiftPoint,@ProductPromotion_Id,@Quantity,GETDATE())
				-- Cập nhật số lượng tồn tặng phẩm
				UPDATE ProductPromotion 
				SET RemainAmount = RemainAmount - @Quantity
			END
		ELSE
			BEGIN
				raiserror(N'Không thể cập nhật số lượng lúc sau nhỏ hơn lúc đầu',16,1)
				return -1
			END

	COMMIT TRAN
-- SV: 1142066 -----------------------------------------
-- Sửa tặng phẩm của 1 chi tiết hóa đơn (BillDetail)
--------------------------------------------------------

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[spUpdateBillDetail]
@Bill_Id int,
@ProductPromotion_Id int,
@Quantity int
as
	BEGIN TRAN
		
		DECLARE @RemainAmount int, @MaxGiftAmount int, 
				@GiftPointRequired int, @Old_Quantity int
		-- Đọc số tặng phẩm tồn, tặng phẩm tối đa, điểm yêu cầu
		SELECT @RemainAmount = RemainAmount,
			   @MaxGiftAmount = MaxGiftAmount,
			   @GiftPointRequired = GiftPointRequired,
			   @Old_Quantity = Quantity
		FROM ProductPromotion
		WHERE Id = @ProductPromotion_Id
		-- Đọc số điểm thưởng
		DECLARE @GiftPoint int
		SELECT @GiftPoint = GiftPoint
		FROM Bill 
		WHERE Id = @Bill_Id 
		-- Nếu thỏa điều kiện nhận tặng phẩm
		IF(@Quantity <= @RemainAmount AND @Quantity <= @MaxGiftAmount AND @GiftPoint >= @GiftPointRequired)
			BEGIN
				-- Thêm chi tiết tặng phẩm
				UPDATE BillDetail SET Quantity = @Quantity
				-- Cập nhật số lượng tồn tặng phẩm
				if(@Old_Quantity < @Quantity)
					SET @RemainAmount = @Quantity - @Old_Quantity
				else 
					SET @RemainAmount =  @Old_Quantity - @Quantity

				UPDATE ProductPromotion 
				SET RemainAmount = @RemainAmount
			END
		ELSE
			BEGIN
				raiserror(N'Không thỏa điều kiện nhận tặng phẩm',16,1)
				return -1
			END

	COMMIT TRAN

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[spProductPromotion_Insert] 
    @Name nvarchar(50),
    @GiftPointRequired int,
    @MaxGiftAmount int,
    @RemainAmount int,
    @Quantity int,
    @GiftOfferedCount int,
    @ProductId int
AS 
	BEGIN TRAN
	
	INSERT INTO [dbo].[ProductPromotion] ([Name], [GiftPointRequired], [MaxGiftAmount], [RemainAmount], [Quantity], [GiftOfferedCount], [ProductId])
	SELECT @Name, @GiftPointRequired, @MaxGiftAmount, @RemainAmount, @Quantity, @GiftOfferedCount, @ProductId
    SELECT * FROM [ProductPromotion] WHERE Id = SCOPE_IDENTITY()                  
	COMMIT
GO

GO

--Indexes of table dbo.Applications
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Applications] ADD CONSTRAINT [PK_Applications] PRIMARY KEY CLUSTERED ([ApplicationId]) 
GO

--Indexes of table dbo.Bill
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Bill] ADD CONSTRAINT [PK_Bill] PRIMARY KEY CLUSTERED ([Id]) 
GO

--Indexes of table dbo.BillDetail
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[BillDetail] ADD CONSTRAINT [PK_DetailBills] PRIMARY KEY CLUSTERED ([Id]) 
GO

--Indexes of table dbo.Memberships
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Memberships] ADD CONSTRAINT [PK_Memberships] PRIMARY KEY CLUSTERED ([UserId]) 
GO

--Indexes of table dbo.ProductPromotion
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[ProductPromotion] ADD CONSTRAINT [PK_ProductPromotion] PRIMARY KEY CLUSTERED ([Id]) 
GO

--Indexes of table dbo.Products
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([Id]) 
GO

--Indexes of table dbo.ProductTypes
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[ProductTypes] ADD CONSTRAINT [PK_ProductTypes] PRIMARY KEY CLUSTERED ([Id]) 
GO

--Indexes of table dbo.Profiles
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Profiles] ADD CONSTRAINT [PK_Profiles] PRIMARY KEY CLUSTERED ([UserId]) 
GO

--Indexes of table dbo.Resellers
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Resellers] ADD CONSTRAINT [PK_Resellers] PRIMARY KEY CLUSTERED ([Id]) 
GO

--Indexes of table dbo.Roles
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED ([RoleId]) 
GO

--Indexes of table dbo.StatusSelling
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[StatusSelling] ADD CONSTRAINT [PK_StatusSeller] PRIMARY KEY CLUSTERED ([Id]) 
GO

--Indexes of table dbo.Users
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserId]) 
GO

--Indexes of table dbo.UsersInRoles
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[UsersInRoles] ADD CONSTRAINT [PK_UsersInRoles] PRIMARY KEY NONCLUSTERED ([Roles_RoleId], [Users_UserId]) 
GO

--Foreign Keys

USE [SuperStar_Nhom11]
GO
ALTER TABLE [dbo].[Bill] WITH CHECK ADD CONSTRAINT [FK_Bill_Products] 
	FOREIGN KEY ([Product_Id]) REFERENCES [dbo].[Products] ([Id])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Bill] CHECK CONSTRAINT [FK_Bill_Products]
GO
ALTER TABLE [dbo].[Bill] WITH CHECK ADD CONSTRAINT [FK_Bills_Reseller] 
	FOREIGN KEY ([Reseller_Id]) REFERENCES [dbo].[Resellers] ([Id])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Bill] CHECK CONSTRAINT [FK_Bills_Reseller]
GO
ALTER TABLE [dbo].[Bill] WITH CHECK ADD CONSTRAINT [FK_Bills_StatusSelling] 
	FOREIGN KEY ([Status_Id]) REFERENCES [dbo].[StatusSelling] ([Id])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Bill] CHECK CONSTRAINT [FK_Bills_StatusSelling]
GO
ALTER TABLE [dbo].[Bill] WITH CHECK ADD CONSTRAINT [FK_Bill_Users] 
	FOREIGN KEY ([Customer_Id]) REFERENCES [dbo].[Users] ([UserId])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Bill] CHECK CONSTRAINT [FK_Bill_Users]
GO
ALTER TABLE [dbo].[BillDetail] WITH CHECK ADD CONSTRAINT [FK_BillDetails_ProductPromotion] 
	FOREIGN KEY ([ProductPromotion_Id]) REFERENCES [dbo].[ProductPromotion] ([Id])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[BillDetail] CHECK CONSTRAINT [FK_BillDetails_ProductPromotion]
GO
ALTER TABLE [dbo].[Memberships] WITH CHECK ADD CONSTRAINT [FK_MembershipApplication] 
	FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[Applications] ([ApplicationId])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [FK_MembershipApplication]
GO
ALTER TABLE [dbo].[Memberships] WITH CHECK ADD CONSTRAINT [FK_MembershipUser] 
	FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([UserId])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [FK_MembershipUser]
GO
ALTER TABLE [dbo].[ProductPromotion] WITH CHECK ADD CONSTRAINT [FK_Promotion_Products] 
	FOREIGN KEY ([ProductId]) REFERENCES [dbo].[Products] ([Id])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[ProductPromotion] CHECK CONSTRAINT [FK_Promotion_Products]
GO
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_ProductTypes] 
	FOREIGN KEY ([ProductType_Id]) REFERENCES [dbo].[ProductTypes] ([Id])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProductTypes]
GO
ALTER TABLE [dbo].[Profiles] WITH CHECK ADD CONSTRAINT [FK_UserProfile] 
	FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([UserId])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Profiles] CHECK CONSTRAINT [FK_UserProfile]
GO
ALTER TABLE [dbo].[Resellers] WITH CHECK ADD CONSTRAINT [FK_Resellers_Users] 
	FOREIGN KEY ([User_Id]) REFERENCES [dbo].[Users] ([UserId])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[Resellers] CHECK CONSTRAINT [FK_Resellers_Users]
GO
ALTER TABLE [dbo].[UsersInRoles] WITH CHECK ADD CONSTRAINT [FK_UsersInRoles_Roles] 
	FOREIGN KEY ([Roles_RoleId]) REFERENCES [dbo].[Roles] ([RoleId])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [FK_UsersInRoles_Roles]
GO
ALTER TABLE [dbo].[UsersInRoles] WITH CHECK ADD CONSTRAINT [FK_UsersInRoles_Users] 
	FOREIGN KEY ([Users_UserId]) REFERENCES [dbo].[Users] ([UserId])
	ON UPDATE NO ACTION
	ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [FK_UsersInRoles_Users]
GO
